package champPackage;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import com.sun.corba.se.spi.orbutil.fsm.State;

public class DBManager {
	static String server = "localhost";
	static String password = "malibu";
	static String account = "root";
	static String database = "mysql";
	private static  Connection con;
	private static Connection con1;
	private static DBManager db;
	 
	private static Object lock = new Object();
	
	public static DBManager getInstance(){
		synchronized (lock) {
			if(db==null) db = new DBManager();
			return db;
		}
	}
	 
	private DBManager(){
		try {
			 Class.forName("com.mysql.jdbc.Driver");
			 con = DriverManager.getConnection("jdbc:mysql://" + server, account, password);
			 con1 = DriverManager.getConnection("jdbc:mysql://" + server, account, password);
		}
		 
		//es eqseption rom ar moxdes conector jar chaagdet web-inf_is lib foldershic
		catch (ClassNotFoundException e) {
			  System.out.println("Class not found Exception!!!!!");
			  e.printStackTrace();
		}
		catch (SQLException e) {
			 System.out.println("SQL Exception");
			 e.printStackTrace();
		}
	}
	
	
public Iterator getTeamPlayers(Team t,Championship ch) throws SQLException{		
		ArrayList<String>list= new ArrayList<String>();
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		int teamId=t.getId();
		//int championshipId=ch.getId();
		ResultSet rs = stmt.executeQuery("Select player_id from teamPlayers where team_id="+teamId+" and championship_id=1;");	
		System.out.println("while-mde");
		while(rs.next()){
			String playerID=rs.getString(1);
			int playerId= Integer.parseInt(playerID);
			Statement stmt1 = con1.createStatement();
			stmt1.executeQuery("use "+database);
			ResultSet rs2 = stmt1.executeQuery("Select * from players where player_id="+playerId+";");
			//System.out.println(rs2==null);
			if(rs2.next());
			String playerName=rs2.getString("player_name");
			System.out.println(playerName+" "+rs2==null);
			Date dabadebisTarigi=Date.valueOf(rs2.getString("date_of_birth"));
			System.out.println(playerName+" "+dabadebisTarigi);
			list.add(playerName);
		}
		return list.iterator();		
	}


	/**
	 * @author Muxo
	 * returns championship Id for new championship
	 * @return Id of new championship
	 * @throws SQLException
	 */
	
	public int getNewChampionshipId() throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		ResultSet rs=stmt.executeQuery("SELECT COUNT(championship_id) FROM championshipInfo");
		rs.next();
		String result=rs.getString("count(championship_id)");
		
		return Integer.parseInt(result)+1;
		
	}

	/**
	 * @author Muxo
	 * creates new championship. adds new championship into userChampionships and championshipInfo Tables
	 * @param id championship ID
	 * @param userName
	 * @throws SQLException
	 */
	public void addNewChampionship(int id, int userName) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("insert into userChampionships values ('"+userName+"', '"+id+"');");
		stmt.executeUpdate("insert into championshipInfo values ('"+id+"', 'XXXX', '-1', '-1', '1900-01-01', '1900-01-01', '0');");
	}
	
	/**
	 * @author Muxo
	 * updates championship_name in championshipInfo
	 * @param id
	 * @param name
	 * @throws SQLException
	 */
	public void addChampionshipName(int id, String name) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("update championshipInfo set championship_name='"+name+"' where championship_id="+id+";");
	}
	
	/**
	 * @author Muxo
	 * updates start_date in championshipInfo
	 * @param id
	 * @param name
	 * @throws SQLException
	 */
	public void addchampionshipStart(int id, String start) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("update championshipInfo set start_date='"+start+"' where championship_id="+id+";");
	}
	
	/**
	 * @author Muxo
	 * updates end_date in championshipInfo
	 * @param id
	 * @param name
	 * @throws SQLException
	 */
	public void addchampionshipend(int id, String end) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("update championshipInfo set end_date='"+end+"' where championship_id="+id+";");
	}
	
	/**
	 * @author Muxo
	 * updates number_of_teams in championshipInfo
	 * @param id
	 * @param name
	 * @throws SQLException
	 */
	public void updateNumberOfTeams(int id, int n) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("update championshipInfo set number_of_teams='"+n+"' where championship_id="+id+";");
	}
	
	/**
	 * @author Muxo
	 * adds new team to the championshipTable;
	 * @param champID
	 * @param teamID
	 * @throws SQLException
	 */
	
	public void addTeamToChampionship(int champID, int teamID) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("insert into championshipTable(championship_ud, team_id) values('"+champID+"','"+teamID+"');");
	}
	
	/**
	 * @author Muxo
	 * removes team from the championshipTable;
	 * @param champID
	 * @param teamID
	 * @throws SQLException
	 */
	
	public void removeTeamfromChampionship(int champID, int teamID) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		stmt.executeUpdate("delete from championshipTable where championship_ud='"+champID+"' and team_id='"+teamID+"';");
	}
	
	/**
	 * @author Muxo
	 * returns the id of champion team
	 * @param champID
	 * @return
	 * @throws SQLException
	 */
	
	public int getChampionTeam(int champID) throws SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		ResultSet rs=stmt.executeQuery("select team_id from championshipTable where championship_ud="+champID+" and place=1;");
		rs.next();
		int result=rs.getInt("team_id");
		return result;
	}
	
	
	/**
	 * @author Muxo
	 * adds new match results to the championshipTable
	 * @param champID
	 * @param firstTeamID
	 * @param secondTeamID
	 * @param firstTeamScore
	 * @param secondTeamScore
	 * @throws SQLException
	 */
	
	public void addNewMatch(int champID, int firstTeamID, int secondTeamID, int firstTeamScore, int secondTeamScore) throws SQLException{
		int firstTeamPoint=0;
		int secondTeamPoint=0;
		int firstTeamWon=0;
		int secondTeamWon=0;
		int firstTeamdraw=0;
		int secondTeamdraw=0;
		int firstTeamLost=0;
		int secondTeamLost=0;
		if(firstTeamScore>secondTeamScore){
			firstTeamPoint=3;
			firstTeamWon=1;
			secondTeamLost=1;
		}else if(secondTeamScore>firstTeamScore){
			secondTeamPoint=3;
			secondTeamWon=1;
			firstTeamLost=1;
		}else{
			firstTeamPoint=1;
			secondTeamPoint=1;
			firstTeamdraw=1;
			secondTeamdraw=1;
		}
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		ResultSet rs=stmt.executeQuery("select * from championshipTable where championship_ud="+champID+" and team_id="+firstTeamID+";");
		rs.next();
		int firstTeamPlayed=rs.getInt("played")+1;
		firstTeamWon+=rs.getInt("won");
		firstTeamdraw+=rs.getInt("draw");
		firstTeamLost+=rs.getInt("lost");
		int firstTeamScored=firstTeamScore+rs.getInt("scored");
		int FirstTeamRecieved=secondTeamScore+rs.getInt("recieved");
		firstTeamPoint+=rs.getInt("point");
		stmt.executeUpdate("update championshipTable set played='"+firstTeamPlayed+"', won='"+firstTeamWon+
				"',draw='"+firstTeamdraw+"',lost='"+firstTeamLost+"',scored='"+firstTeamScored+"', recieved='"+
				FirstTeamRecieved+"', point='"+firstTeamPoint+"' where championship_ud="+champID+" and team_id="+firstTeamID+";");
		rs=stmt.executeQuery("select * from championshipTable where championship_ud="+champID+" and team_id="+secondTeamID+";");
		rs.next();
		int secondTeamPlayed=rs.getInt("played")+1;
		secondTeamWon+=rs.getInt("won");
		secondTeamdraw+=rs.getInt("draw");
		secondTeamLost+=rs.getInt("lost");
		int secondTeamScored=secondTeamScore+rs.getInt("scored");
		int secondTeamRecieved=firstTeamScore+rs.getInt("recieved");
		secondTeamPoint+=rs.getInt("point");
		stmt.executeUpdate("update championshipTable set played='"+secondTeamPlayed+"', won='"+secondTeamWon+
				"', draw='"+secondTeamdraw+"',lost='"+secondTeamLost+"',scored='"+secondTeamScored+"', recieved='"+
				secondTeamRecieved+"', point='"+secondTeamPoint+"' where championship_ud="+champID+" and team_id="+secondTeamID+";");
		
		sortChampionshipTable(champID);
		
	}
	
	/**
	 * @author Muxo
	 * sorts championshipTable and updates place column in the table
	 * @param champID
	 * @throws SQLException 
	 */

	private void sortChampionshipTable(int champID) throws SQLException {
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		ResultSet rs=stmt.executeQuery("select team_id from championshipTable where championship_ud="+champID+" order by point desc");;
		int place=1;
		int id=0;
		ArrayList<Integer> list=new ArrayList<Integer>();
		while (rs.next()){
			id=rs.getInt("team_id");
			list.add(id);
		}
		
		for(int i=0;i<list.size();i++){
			stmt.executeUpdate("update championshipTable set place="+place+" where team_id="+list.get(i)+";");
			place++;
		}
	}
	
	
	
	/**
	 * @author Tengo
	 * this boolean checks whether the userName and password matches the one in dataBase
	 * @throws NoSuchAlgorithmException 
	 * @throws SQLException 
	 * 
	 */
	public boolean isCorrectUser(String userName, String password) throws NoSuchAlgorithmException, SQLException{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		ResultSet rs = stmt.executeQuery("select user_password from userInfo where user_name='"+userName+"';");
		if(!rs.next())return false;
		
		System.out.println(userName+" ai esaa gadmocemuli username");
		String hashValue=rs.getString(1);
		MessageDigest digest=  MessageDigest.getInstance("SHA");
		digest.update(password.getBytes());
		byte[]result=digest.digest();
		String kodi=hexToString(result);
		if(hashValue.equals(kodi))return true;		
		return false;
	}
	

/**
	public static void main(String[] args) throws SQLException {
		DBManager db = DBManager.getInstance();
//		Team t = new Team("Lasha");
//		Championship ch = new Championship(null);
//		Iterator<String> it = db.getTeamPlayers(t, ch);
//		while(it.hasNext()){
//			String s = it.next();
//			System.out.println(s);
//		}
		System.out.println(db.getNewChampionshipId());
		db.addNewChampionship(48, 1);
	}
*/
	
	public void addUserInfoToTheDatabase(String user, String password, String email, String type){
		try{
		Statement stmt = con.createStatement();
		stmt.executeQuery("use " + database);
		MessageDigest digest = MessageDigest.getInstance("SHA");
		digest.update(password.getBytes());
		byte[]result=digest.digest();
		String kodi=hexToString(result);
		stmt.executeUpdate("insert into userInfo values ('"+user+"', '"+type+"', '"+kodi+"','"+email+"');");
		}
		catch(SQLException ex){ ex.printStackTrace(); }
		catch(NoSuchAlgorithmException ex){ ex.printStackTrace(); }
	}
	
	public void updateChampionshipTableLine(TableLine line){
		try{
			Statement stmt = con.createStatement();
			stmt.executeQuery("use "+database);
			stmt.executeUpdate("update championshipTable set played="+line.getPlayed()+", won="+line.getWon()+", draw="+line.getDraws()+
					", lost="+line.getLost()+", scored="+line.getScored()+", recieved="+line.getReceived()+", point="+line.getPoint()+" where championship_ud="+
					line.getChampionhipID()+" and team_id="+line.getTeamID());
		}
		catch(SQLException ex){
			System.out.println("deda sheveci");
			ex.printStackTrace();
		}
	}
	
	
	
	/**
	 * 
	 * @param champ_id  The id of a championship
	 * @param team_id The id of a team
	 * Returns the information about that team in that championship, including the whole line: played, won,
	 * drawn, lost, scored, received, point etc
	 */
	public TableLine getTableLine(int champ_id, int team_id){
		try{
			Statement stmt = con.createStatement();
			stmt.executeQuery("use "+database);
			ResultSet rset = stmt.executeQuery("select * from championshipTable where championship_ud='"+champ_id+"' and team_id='"
					+team_id+"';");
			rset.next();
			int played = Integer.parseInt(rset.getString(3)), won = Integer.parseInt(rset.getString(4)), draw = 
					Integer.parseInt(rset.getString(5)), lost = Integer.parseInt(rset.getString(6)), scored = 
					Integer.parseInt(rset.getString(7)), received = Integer.parseInt(rset.getString(8)), point = 
					Integer.parseInt(rset.getString(9));
			TableLine line = new TableLine(champ_id, team_id);
			line.setPlayed(played);
			line.setWon(won);
			line.setDraws(draw);
			line.setLost(lost);
			line.setScored(scored);
			line.setReceived(received);
			line.setPoint(point);
			return line;
		}
		catch(SQLException ex){
			ex.printStackTrace();
			System.out.println("hgjgjh");
			return null;
		}
	}
	
	/*
	 Given a byte[] array, produces a hex String,
	 such as "234a6f". with 2 chars for each byte in the array.
	 (provided code)
	*/
	public static String hexToString(byte[] bytes) {
		StringBuffer buff = new StringBuffer();
		for (int i=0; i<bytes.length; i++) {
			int val = bytes[i];
			val = val & 0xff;  // remove higher bits, sign
			if (val<16) buff.append('0'); // leading 0
			buff.append(Integer.toString(val, 16));
		}
		return buff.toString();
	}
	
}
